{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "import sys\n",
    "import pandas as pd # type: ignore\n",
    "from datetime import datetime, date\n",
    "\n",
    "# Dataframe设置不显示小数点\n",
    "pd.set_option('display.precision', 0)\n",
    "\n",
    "File_Path = os.path.join(os.path.expanduser(\"~\"), 'Desktop') + \"\\\\每日动环\\\\3.0\"\n",
    "# 定义地点和等级的期望顺序\n",
    "df_index = ['一级', '二级', '三级', '四级']\n",
    "levels_order = ['一级告警', '二级告警', '三级告警', '四级告警']\n",
    "\n",
    "locations_order = [\n",
    "    '广州', '深圳', '东莞', '佛山', '中山', '惠州', '珠海', '江门', '汕头', '湛江', '揭阳','肇庆', '韶关', '清远', '茂名', '梅州', '潮州', '阳江', '河源', '汕尾', '云浮', '科学城', '松山湖', '白云北', '知识城'\n",
    "]\n",
    "alarm = {'重要告警': '二级', '普通告警': '三级', '紧急告警': '一级', '一般告警': '四级'}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "def znv(res_zvn, region_out):\n",
    "    global locations_order, df_index\n",
    "    # 监控中心归类地市\n",
    "    res_zvn['监控中心'] = res_zvn['监控中心'].str[:2]\n",
    "    res_zvn = res_zvn[~res_zvn['监控中心'].isin(['佛山', '湛江', '梅州', '河源', '汕尾'])]\n",
    "\n",
    "    # 统计告警等级数据\n",
    "    result = res_zvn.groupby(['监控中心', '告警级别']).size().unstack(fill_value=0)\n",
    "    if type(region_out) != int:\n",
    "        result = pd.concat([result, region_out])\n",
    "    # 重新排序，按指定顺序排序行列\n",
    "    output = result.reindex(index=locations_order, columns=df_index)\n",
    "    for i in ['惠州', '揭阳', '梅州', '潮州', '韶关', '白云北', '知识城']:\n",
    "        output.loc[i] = '/'\n",
    "    # 将NAN空值设为0\n",
    "    output.fillna(0, inplace=True)\n",
    "    return output\n",
    "\n",
    "def kfs(path,name):\n",
    "    global df_index\n",
    "    df = pd.read_excel(path, header=0)\n",
    "    kfs = df['级别 (level)'].value_counts().to_dict()\n",
    "    kfs = pd.DataFrame(kfs, index=[name.split('.')[0]])\n",
    "    out = pd.DataFrame(kfs).reindex(columns=df_index)\n",
    "    # 将NAN空值设为0\n",
    "    out.fillna(0, inplace=True)\n",
    "    return out\n",
    "\n",
    "\n",
    "def Shenzhen(path, name):\n",
    "    global locations_order, df_index, alarm\n",
    "    Local_City = {}\n",
    "    df = pd.read_excel(path, header=6)\n",
    "    df['告警级别'] = df['告警级别'].map(alarm)\n",
    "    Local_City[name.split('.')[0]] = dict(df['告警级别'].value_counts())\n",
    "    out = pd.DataFrame(Local_City).T.reindex(columns=df_index)\n",
    "    # 将NAN空值设为0\n",
    "    out.fillna(0, inplace=True)\n",
    "    return out\n",
    "\n",
    "\n",
    "def vertiv(SC):\n",
    "    global locations_order, levels_order, df_index, alarm\n",
    "    # 定义地点映射字典\n",
    "    location_map = {\n",
    "        '广州联通': '广州',\n",
    "        '江门联通维谛动环监控系统': '江门',\n",
    "        '佛山联通': '佛山',\n",
    "        '肇庆联通': '肇庆',\n",
    "        '中山联通动环监控': '中山',\n",
    "        '湛江联通': '湛江',\n",
    "        '汕头联通': '汕头',\n",
    "        '中国联通华南（东莞）数据中心二期': '松山湖',\n",
    "        '惠州联通动环监控系统': '惠州',\n",
    "        '珠海联通监控中心': '珠海',\n",
    "        '韶关联通': '韶关',\n",
    "        '广州白云联通IDC': '白云北',\n",
    "        '东莞联通监控中心': '东莞',\n",
    "        '揭阳联通动环监控': '揭阳',\n",
    "        '梅州联通动环监控': '梅州',\n",
    "        '潮州联通监控中心': '潮州',\n",
    "        '广州联通知识城': '知识城',\n",
    "        '阳江联通IDC机房': '阳江',\n",
    "        '汕尾联通': '汕尾',\n",
    "        '清远联通': '清远'\n",
    "    }\n",
    "\n",
    "    # 使用map函数进行地点名称的映射\n",
    "    SC['监控中心'] =  SC['监控中心'].map(location_map)\n",
    "\n",
    "    # 使用groupby进行统计\n",
    "    result = SC.groupby(['监控中心', '事件等级']).size().unstack(fill_value=0)\n",
    "\n",
    "    # 根据定义的顺序重排DataFrame的行和列\n",
    "    output = result.reindex(index=locations_order, columns=levels_order)\n",
    "    # 对于没有数据的监控中心，填充为/\n",
    "    for i in ['深圳','东莞','佛山','中山','茂名','河源','云浮','科学城']:\n",
    "        output.loc[i] = '/'\n",
    "\n",
    "    # 将NAN空值设为0\n",
    "    output.fillna(0, inplace=True)\n",
    "\n",
    "    return output\n",
    "def history(new_path):\n",
    "    '''3.0'''\n",
    "    df = pd.read_excel(new_path, header=0)\n",
    "    gateway = df['所属计算节点 (gateway)'].value_counts().to_dict()\n",
    "    gateway['科学城DCIM北向'] = gateway.pop('广州科学城DCIM北向')\n",
    "    gateway['白云北维谛'] = gateway.pop('广州白云北维谛')\n",
    "    gateway['知识城维谛'] = gateway.pop('广州知识城维谛')\n",
    "    gateway['松山湖C'] = gateway.pop('东莞松山湖北向')\n",
    "    C = {}\n",
    "    api = {}\n",
    "    for i in gateway:\n",
    "        if 'C' in i:\n",
    "            if i[:2] in C:\n",
    "                C[i[:2]] = gateway[i] + C[i[:2]]\n",
    "            else:\n",
    "                C[i[:2]] = gateway[i]\n",
    "        elif '维谛' in i:\n",
    "            api[i[:2]] = gateway[i]\n",
    "    locations_order = [\n",
    "        '广州', '深圳', '东莞', '佛山', '中山', '惠州', '珠海', '江门', '汕头', '湛江', '揭阳', '肇庆',\n",
    "        '韶关', '清远', '茂名', '梅州', '潮州', '阳江', '河源', '汕尾', '云浮', '科学', '松山', '白云',\n",
    "        '知识'\n",
    "    ]\n",
    "    gateway_C = pd.DataFrame(C, index=['C接口']).T.reindex(index=locations_order)\n",
    "    gateway_api = pd.DataFrame(api,\n",
    "                               index=['API']).T.reindex(index=locations_order)\n",
    "    # gateway_api.loc['松山'] = songshan\n",
    "    gateway = pd.concat([gateway_C, gateway_api], axis=1)\n",
    "\n",
    "    gateway.fillna('/', inplace=True)\n",
    "    gateway = gateway.rename(index={\n",
    "        '科学': '科学城',\n",
    "        '松山': '松山湖',\n",
    "        '白云': '白云北',\n",
    "        '知识': '知识城'\n",
    "    })\n",
    "    return gateway"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "def main():\n",
    "    global Science_City, Foshan, Songshan, Shenzhen\n",
    "    names = os.listdir(File_Path)\n",
    "    Local_City = {}\n",
    "    SC = res_zvn = region_out = kfs_out = gateway =0\n",
    "    for name in names:\n",
    "        path = os.path.join(File_Path, name)\n",
    "        try:\n",
    "            if \"devicealarmdetail\" in name:\n",
    "                if type(res_zvn) == int:\n",
    "                    res_zvn = pd.read_excel(path, header=6)\n",
    "                else:\n",
    "                    res_zvn = pd.concat(\n",
    "                        [res_zvn, pd.read_excel(path, header=6)],\n",
    "                        ignore_index=True)\n",
    "            elif 'SC' in name:\n",
    "                print('数据为空时,一定要打开SC文件并保存一下。')\n",
    "                if type(SC) == int:\n",
    "                    SC = pd.read_excel(path)\n",
    "                else:\n",
    "                    SC = pd.concat([SC, pd.read_excel(path)],\n",
    "                                   ignore_index=True)\n",
    "            elif '科学城' in name:\n",
    "                if type(kfs_out) == int:\n",
    "                    kfs_out = kfs(path, name)\n",
    "                else:\n",
    "                    kfs_out = pd.concat([kfs_out, kfs(path, name)])\n",
    "            elif '佛山' in name:\n",
    "                if type(kfs_out) == int:\n",
    "                    kfs_out = kfs(path, name)\n",
    "                else:\n",
    "                    kfs_out = pd.concat([kfs_out, kfs(path, name)])\n",
    "            elif '松山湖' in name:\n",
    "                if type(kfs_out) == int:\n",
    "                    kfs_out = kfs(path, name)\n",
    "                else:\n",
    "                    kfs_out = pd.concat([kfs_out, kfs(path, name)])\n",
    "            elif '深圳' in name:\n",
    "                if type(kfs_out) == int:\n",
    "                    kfs_out = Shenzhen(path, name)\n",
    "                else:\n",
    "                    kfs_out = pd.concat([kfs_out, Shenzhen(path, name)])\n",
    "            elif '历史' in name:\n",
    "                gateway = history(path)\n",
    "            else:\n",
    "                df = pd.read_excel(path, header=6)\n",
    "                Local_City[name.split('.')[0]] = dict(\n",
    "                    df['告警级别'].value_counts())\n",
    "                region_out = pd.DataFrame(Local_City).T.reindex(\n",
    "                    columns=df_index)\n",
    "        except Exception as e:\n",
    "            print(f'{name}文件读取失败，原因：{e}')\n",
    "    ont_zvn = znv(res_zvn, region_out)\n",
    "    if type(kfs_out) != int:\n",
    "        for i in kfs_out.index:\n",
    "            ont_zvn.loc[i] = kfs_out.loc[i]\n",
    "    ont_zvn['力维动环'] = ont_zvn.sum(axis=1, skipna=True)\n",
    "    res_vert = vertiv(SC)\n",
    "    res_vert['维谛动环'] = res_vert.sum(axis=1, skipna=True)\n",
    "    output = pd.concat([ont_zvn, res_vert, gateway], axis=1)\n",
    "\n",
    "    return output"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "数据为空时,一定要打开SC文件并保存一下。\n",
      "数据为空时,一定要打开SC文件并保存一下。\n",
      "      一级    二级   三级   四级  力维动环 一级告警 二级告警  三级告警 四级告警  维谛动环   C接口   API\n",
      "广州    13    12   26   24    75    1  463  1195   64  1822    75  1644\n",
      "深圳    32  1165  308    9  1514    /    /     /    /  ////  1473     /\n",
      "东莞   109    22   57  205   393    /    /     /    /  ////   365     /\n",
      "佛山     0     0    0    0     0    /    /     /    /  ////  4116     /\n",
      "中山    48     3   17  174  1808    /    /     /    /  ////  1831    32\n",
      "惠州     /     /    /    /  ////    9   13    31         53     /    59\n",
      "珠海           3  261    2   266    3    3   773        779   262   779\n",
      "江门     5   452    6    4   467    1   18    41         69   467   108\n",
      "汕头     1    23   36   33    93         7   182        189   102   189\n",
      "湛江    71    33    9   16   129         4    11         15   129    15\n",
      "揭阳     /     /    /    /  ////    0    0     0    0     0     /    43\n",
      "肇庆    29    26   34    6    95         3                3    95     3\n",
      "韶关     /     /    /    /  ////    2   43    84   14   143     /   143\n",
      "清远    15    15   36    0    66    3   16    49         68    66    68\n",
      "茂名     3    38   47   11   198    /    /     /    /  ////   267     /\n",
      "梅州     /     /    /    /  ////    4   16   536    1   566     2   566\n",
      "潮州     /     /    /    /  ////    8    9   384        401     /   401\n",
      "阳江    17     3    1    3    24    0    0     0    0     0    24     /\n",
      "河源     2    12   12  192   218    /    /     /    /  ////   218     /\n",
      "汕尾     0    59    8    0    67    7   36    12    1    56    67    56\n",
      "云浮     4     2  149   79   234    /    /     /    /  ////   235     /\n",
      "科学城    0     0    0    0     0    /    /     /    /  ////   192     /\n",
      "松山湖    0     0    0    0     0               1          1    18     /\n",
      "白云北    /     /    /    /  ////    0    0     0    0     0     /   151\n",
      "知识城    /     /    /    /  ////               9    8    17     /    17\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\00\\AppData\\Local\\Temp\\ipykernel_740\\3167129742.py:113: FutureWarning: Setting an item of incompatible dtype is deprecated and will raise an error in a future version of pandas. Value '/' has dtype incompatible with float64, please explicitly cast to a compatible dtype first.\n",
      "  gateway.fillna('/', inplace=True)\n",
      "C:\\Users\\00\\AppData\\Local\\Temp\\ipykernel_740\\3167129742.py:14: FutureWarning: Setting an item of incompatible dtype is deprecated and will raise an error in a future version of pandas. Value '/' has dtype incompatible with float64, please explicitly cast to a compatible dtype first.\n",
      "  output.loc[i] = '/'\n",
      "C:\\Users\\00\\AppData\\Local\\Temp\\ipykernel_740\\3167129742.py:14: FutureWarning: Setting an item of incompatible dtype is deprecated and will raise an error in a future version of pandas. Value '/' has dtype incompatible with float64, please explicitly cast to a compatible dtype first.\n",
      "  output.loc[i] = '/'\n",
      "C:\\Users\\00\\AppData\\Local\\Temp\\ipykernel_740\\3167129742.py:14: FutureWarning: Setting an item of incompatible dtype is deprecated and will raise an error in a future version of pandas. Value '/' has dtype incompatible with float64, please explicitly cast to a compatible dtype first.\n",
      "  output.loc[i] = '/'\n",
      "C:\\Users\\00\\AppData\\Local\\Temp\\ipykernel_740\\3167129742.py:14: FutureWarning: Setting an item of incompatible dtype is deprecated and will raise an error in a future version of pandas. Value '/' has dtype incompatible with float64, please explicitly cast to a compatible dtype first.\n",
      "  output.loc[i] = '/'\n",
      "C:\\Users\\00\\AppData\\Local\\Temp\\ipykernel_740\\3167129742.py:78: FutureWarning: Setting an item of incompatible dtype is deprecated and will raise an error in a future version of pandas. Value '/' has dtype incompatible with float64, please explicitly cast to a compatible dtype first.\n",
      "  output.loc[i] = '/'\n",
      "C:\\Users\\00\\AppData\\Local\\Temp\\ipykernel_740\\3167129742.py:78: FutureWarning: Setting an item of incompatible dtype is deprecated and will raise an error in a future version of pandas. Value '/' has dtype incompatible with float64, please explicitly cast to a compatible dtype first.\n",
      "  output.loc[i] = '/'\n",
      "C:\\Users\\00\\AppData\\Local\\Temp\\ipykernel_740\\3167129742.py:78: FutureWarning: Setting an item of incompatible dtype is deprecated and will raise an error in a future version of pandas. Value '/' has dtype incompatible with float64, please explicitly cast to a compatible dtype first.\n",
      "  output.loc[i] = '/'\n",
      "C:\\Users\\00\\AppData\\Local\\Temp\\ipykernel_740\\3167129742.py:78: FutureWarning: Setting an item of incompatible dtype is deprecated and will raise an error in a future version of pandas. Value '/' has dtype incompatible with float64, please explicitly cast to a compatible dtype first.\n",
      "  output.loc[i] = '/'\n"
     ]
    }
   ],
   "source": [
    "if __name__ == '__main__':\n",
    "    print(main())"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.11.9"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
